package de.gwtprime.server.sql;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.Queue;
import java.util.Stack;

import org.apache.log4j.Logger;

import de.gwtprime.util.Helper;


/**
 * Reading the stock data from yahoo! and store them in the database.
 * Is called every day by the importStockData-Servlet to keep the database up-to-date.
 * @author jan
 *
 */
public class StockReader {

	/**
	 * Logger
	 */
	private final static Logger logger = Logger.getLogger(StockReader.class.getName());

	/**
	 * This webservice from yahoo is used to retrieve the stock data.
	 */
	private final String YAHOO_SERVICE = "http://ichart.finance.yahoo.com/table.csv?s=%s&a=%s&b=%s&c=%s&d=%s&e=%s&f=%signore=.cvs";
	
	/**
	 * This DateFormat is used in the data coming from yahoo.
	 */
	private final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	
	/**
	 * This statement is used to store the fetched stock values into the db.
	 */
	private final String INSERT_STATEMENT = "Insert into stockvalues (symbol, date, openValue, closeValue, low, high, durchschnitt200tage) Values";
	
	/**
	 * This statement is used to fetch the date of the last entry for a given symbol. This is needed to only reed newer entries.
	 */
	private final String GET_LAST_ENTRY = "SELECT MAX(date) FROM stockvalues WHERE symbol = '%s'";
	
	Stack<String> stack = new Stack<String>();

	/**
	 * This method is called by the import Servlet.
	 * @return
	 */
	public static int readSingleStock() {
		try {
			return new StockReader().getData("%5EGDAXI", "DAX", "DAX");
		} catch (Exception e) {
			logger.error("Could not read data", e);
			return 0; 
		}
	}

	public void getData(String symbol, String displayName, Date startDate, Date endDate) throws Exception{
		getData(symbol,symbol,startDate,endDate);

	}

	/**
	 * Get latest entry for one stock in the db.
	 * Needed to only import newer values 
	 *
	 */
	public String getLatestValueForSymbol(String symbol) throws SQLException{
		try{
			ResultSet result = SQLConnect.getInstance().select(String.format(GET_LAST_ENTRY, symbol));
	
			if(result.next())
				return result.getString("MAX(date)");
			else
				return null;
		}catch(SQLException e){
			logger.error("Could not get max date for: "+symbol, e);
			throw e;
		}

	}

	/**
	 * This methods computes a start and a end date and reads the values in between from google.
	 * @param symbol
	 * @param symbolToStore
	 * @param displayName
	 * @return
	 * @throws Exception
	 */
	public int getData(String symbol, String symbolToStore, String displayName) throws Exception{
		
		// End date is the current date.
		Date endDate = new Date();
		
		String startDateString = getLatestValueForSymbol(symbolToStore);

		Date startDate = null;
		try{
			
			// Parse date
			startDate = dateFormat.parse(startDateString);
			
			// increase date by one.
			startDate = Helper.addOneDayToDate(startDate);
			
		}catch(Exception e){
			// if no valid start date -> no entry for that stock yet -> generate one:
			startDate = getDefaultStartDate();
		}
		
		return this.getData(symbol, symbolToStore, displayName, startDate, endDate);
	}
	
	
	/**
	 * Reads all stock data between the given start- and enddate from the yahoo webservice.
	 * @param symbol
	 * @param symbolToStore
	 * @param displayName
	 * @param startDate
	 * @param endDate
	 * @return
	 * @throws Exception
	 */
	public int getData(String symbol, String symbolToStore, String displayName, Date startDate, Date endDate) throws Exception{
		
		// Every row is stored in this variable.
		String[] singleRow = null;

				
		String query = INSERT_STATEMENT;


		// no valid dates are giben
		if(startDate.compareTo(endDate)>0){
			logger.error("Could import stock values -> Start date is bigger then end date!");
			return 0;
		}


		Double closeValue =0.0;
		Double durchschnitt = 0.0;
		Double durchschnittWithoutFirst200 = 0.0;
		Queue<Double> queue = new LinkedList<Double>(); 

		URL yahooUrl = getDataFromYahoo(symbol, startDate, endDate);

		
		// Check the url-connection
		HttpURLConnection urlConnection = (HttpURLConnection) yahooUrl.openConnection();
		if(urlConnection.getResponseCode() != HttpURLConnection.HTTP_OK){
			logger.error("No connection possible to: "+yahooUrl+", Status: "+urlConnection.getResponseCode());
			return 0;
		}
		
		int readRows = fillStackWithYahooData(yahooUrl);
		if(readRows==0) return 0;
		
		
		int size = stack.size();
		for(int j=0;j<size;j++){

			String inputLine2 = stack.pop();
			singleRow = inputLine2.split(",");



			// split up the line
			String rowDateString = singleRow[0];
			String rowOpenValueString = singleRow[1];
			String rowCloseValueString = singleRow[4];
			String rowLowValueString = singleRow[3];
			String rowHighValueString = singleRow[2];

			closeValue = Double.parseDouble(rowCloseValueString);

			queue.offer(closeValue/200);
			durchschnitt += closeValue/200;


			durchschnittWithoutFirst200 = 0.0;
			if(queue.size()==200){
				durchschnittWithoutFirst200 = durchschnitt;
				Double old = queue.poll();
				durchschnitt -= old;
			}

			query += String.format("('%s', '%s', %s,  %s,  %s,  %s, %s),", symbolToStore, dateFormat.format(dateFormat.parse(rowDateString)), rowOpenValueString, rowCloseValueString, rowLowValueString, rowHighValueString, durchschnittWithoutFirst200);
			
			// split up in junks of 200 rows?
			if(j%200==0 && j!=0){
				query = query.substring(0, query.length() - 1);
				SQLConnect.getInstance().update(query);
				query =INSERT_STATEMENT;
			}
		}

		query = query.substring(0, query.length() - 1);
		int affectedRows = SQLConnect.getInstance().update(query);

		logger.info("Read rows: "+affectedRows);

		return affectedRows;
	}




	/**
	 * Read in the data from yahoo and store it in the stack.
	 * @param yahooUrl
	 * @return Number of read rows
	 */
	private int fillStackWithYahooData(URL yahooUrl) {
		int i=0;
		BufferedReader in = null;
		try{
			in = new BufferedReader(new InputStreamReader(yahooUrl.openStream()));
			String inputLine;
	
			
	
			while ((inputLine = in.readLine()) != null) {
				if(i!=0){
	
					stack.push(inputLine);
	
				}
				i++;
			}
	
			if(i<2){
				logger.error("Nothing was in the yahoo file");
				return 0;
			}
		}catch(Exception e){
			logger.error("Error while reading data from yahoo", e);
		}finally{
			
			try {
				in.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return i;
	}




	/**
	 * If there is no start date yet (new stock for example) -> generate one
	 * @return
	 */
	private Date getDefaultStartDate() {
		
		Calendar myCal1 = Calendar.getInstance();  
		myCal1.set(Calendar.YEAR, 1990);
		myCal1.set(Calendar.MONTH, 2);
		myCal1.set(Calendar.DAY_OF_MONTH, 24);
		Date startDate = myCal1.getTime();
		return startDate;
	}


//	private void storeStockSymbol(String symbol, String displayName, Double allTimeHeight) throws SQLException {
//		//String sql = String.format("INSERT INTO stocks (symbol, DisplayName, allTimeHeight) VALUES('%s', '%s', %s)", symbol, displayName, allTimeHeight);
//		//SQLConnect.getInstance().update(sql);
//	}


	/**
	 * Read stock symbols from file
	 * @throws Exception
	 */
	public void readSymbols() throws Exception{

		BufferedReader in = null;
		try{
			in = new BufferedReader(
					new InputStreamReader(
							new FileInputStream("C:\\symbols.txt")));


			String inputLine;


			int i=0;


			while ((inputLine = in.readLine()) != null) {

				String splittedLine[] = inputLine.split("\t");

				String displayName = splittedLine[0];
				String symbol = splittedLine[2];

				if(i!=0)
					new StockReader().getData(symbol, displayName, null, new Date()); // get data for this stock

				i++;
			}
		}catch(Exception e){
			logger.error("Error while reading data", e);
		}finally{
			
			Helper.safeCloseReader(in);
		}
		

	}

	
	/**
	 * Build the url to get the data from yahoo.
	 * @param symbol Symbol of the stock to read.
	 * @param startDate First day to read.
	 * @param endDate Last day to read
	 * @return the url to fetch the data from yahoo.
	 */
	public URL getDataFromYahoo(String symbol, Date startDate, Date endDate){

		// split up the start date
		Calendar startCal = Calendar.getInstance();
		startCal.setTime(startDate);
		int startDay = startCal.get(Calendar.DAY_OF_MONTH);
		int startMonth = startCal.get(Calendar.MONTH);
		int startYear = startCal.get(Calendar.YEAR);


		// split up the end date.
		Calendar endCal = Calendar.getInstance();
		endCal.setTime(endDate);
		int endDay = endCal.get(Calendar.DAY_OF_MONTH);
		int endMonth = endCal.get(Calendar.MONTH);
		int endYear = endCal.get(Calendar.YEAR);

		

		// Fill the yahoo url with parameters.
		String url = String.format(YAHOO_SERVICE,symbol, startMonth, startDay, startYear, endMonth, endDay, endYear);


		// Create the url from the string
		URL urlOfYahooWebservice = null;
		try {
			urlOfYahooWebservice = new URL(url);
		} catch (MalformedURLException e) {
			logger.error("Url to call yahoo webservice is not correct", e);
		}

		return urlOfYahooWebservice;
	}
}
